package at.spengergasse.dao.derby;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import at.spengergasse.dao.KundenDao;
import at.spengergasse.dao.exception.DAOException;
import at.spengergasse.dao.exception.NotFoundException;
import at.spengergasse.dao.util.JDBCHelper;
import at.spengergasse.model.Kunden;

public class KundenDaoDerbyImpl extends AbstractDAODerbyImpl implements
		KundenDao
{
	private static final String SQL_INSERT = "INSERT INTO Kunden(k_mod_ts, k_email,"
			+ "k_anrede, k_name, k_pw, k_strasse, k_plz_id) "
			+ "VALUES (?, ?, ?, ?, ?, ?, ?)";

	private static final String SQL_UPDATE = "UPDATE Kunden "
			+ "SET k_mod_ts = ?, " + "k_email = ?, " + "k_anrede = ?, "
			+ "k_name = ?, " + " k_pw = ?, " + " k_strasse = ?, "
			+ "k_plz_id = ? " + "WHERE k_id = ? " + "  AND k_mod_ts = ? ";

	private static final String SQL_DELETE = "DELETE FROM Kunden "
			+ "WHERE k_id = ? " + "  AND k_mod_ts = ? ";

	private static final String SQL_SELECT_BY_ID = "SELECT  k_id, k_mod_ts,  k_email,"
			+ "k_anrede, k_name, k_pw, k_strasse, k_plz_id  "
			+ "FROM Kunden "
			+ "WHERE k_id = ?";

	private static final String SQL_SELECT_ALL = "SELECT k_id, k_mod_ts,  k_email,"
			+ "k_anrede, k_name, k_pw, k_strasse, k_plz_id from kunden";

	private PreparedStatement insertStmt = null;
	private PreparedStatement updateStmt = null;
	private PreparedStatement deleteStmt = null;
	private PreparedStatement selectByIdStmt = null;
	private PreparedStatement selectAllStmt = null;

	@Override
	public int delete(Connection connection, Kunden kunden)
	{
		validateConnection(connection);
		validateKunden(kunden);

		int recordsAffected = 0;

		try
		{
			if (deleteStmt == null)
			{
				deleteStmt = connection.prepareStatement(SQL_DELETE);
			}

			deleteStmt.setLong(1, kunden.getId());
			deleteStmt.setTimestamp(2, JDBCHelper.toSqlTimestamp(kunden
					.getLastModifiedTS()));

			recordsAffected = deleteStmt.executeUpdate();
		} catch (SQLException e)
		{
			throw new DAOException("Customer could not be deleted!", e);
		}
		return recordsAffected;
	}

	@Override
	public List<Kunden> findAll(Connection connection)
	{
		validateConnection(connection);

		List<Kunden> foundKunden = new ArrayList<Kunden>();
		Kunden foundKunde = null;
		ResultSet rs = null;

		try
		{
			if (selectAllStmt == null)
			{
				selectAllStmt = connection.prepareStatement(SQL_SELECT_ALL);
			}

			rs = selectAllStmt.executeQuery();

			while (rs.next())
			{
				foundKunde = new Kunden(rs.getLong(1), rs.getString(3), rs
						.getString(4), rs.getString(5), rs.getString(6), rs
						.getString(7), rs.getLong(8));
				foundKunde.setLastModifiedTS(JDBCHelper.toDate(rs
						.getTimestamp(2)));
				foundKunden.add(foundKunde);
			}
		} catch (SQLException e)
		{
			throw new NotFoundException("Customer could not be read!", e);
		} finally
		{
			closeResultSet(rs);
		}

		return foundKunden;
	}

	@Override
	public Kunden findById(Connection connection, Long id)
	{
		validateConnection(connection);
		if (id == null)
			throw new IllegalArgumentException("Id darf nicht null sein!");

		Kunden foundKunde = null;
		ResultSet rs = null;
		try
		{
			if (selectByIdStmt == null)
			{
				selectByIdStmt = connection.prepareStatement(SQL_SELECT_BY_ID);
			}

			selectByIdStmt.setLong(1, id);
			rs = selectByIdStmt.executeQuery();

			while (rs.next())
			{
				foundKunde = new Kunden(id, rs.getString(3), rs.getString(4),
						rs.getString(5), rs.getString(6), rs.getString(7), rs
								.getLong(8));
				foundKunde.setLastModifiedTS(JDBCHelper.toDate(rs
						.getTimestamp(2)));
			}
		} catch (SQLException e)
		{
			throw new NotFoundException("Customer could not be found!", e);
		} finally
		{
			closeResultSet(rs);
		}

		return foundKunde;
	}

	@Override
	public int save(Connection connection, Kunden kunden)
	{
		validateConnection(connection);
		validateKunden(kunden);
		return (kunden.isNew()) ? insert(connection, kunden) : update(
				connection, kunden);
	}

	private int update(Connection connection, Kunden kunden)
	{
		validateConnection(connection);
		validateKunden(kunden);

		int recordsAffected = 0;
		Date now = new Date();

		try
		{
			if (updateStmt == null)
			{
				updateStmt = connection.prepareStatement(SQL_UPDATE);
			}

			updateStmt.setTimestamp(1, JDBCHelper.toSqlTimestamp(now));
			updateStmt.setString(2, kunden.getEMAIL());
			updateStmt.setString(3, kunden.getANREDE());
			updateStmt.setString(4, kunden.getNAME());
			updateStmt.setString(5, kunden.getPASSWORT());
			updateStmt.setString(6, kunden.getSTRASSE());
			updateStmt.setLong(7, kunden.getPlzId());
			updateStmt.setLong(8, kunden.getId());
			updateStmt.setTimestamp(9, JDBCHelper.toSqlTimestamp(kunden
					.getLastModifiedTS()));

			recordsAffected = updateStmt.executeUpdate();

			kunden.setLastModifiedTS(now);
		} catch (SQLException e)
		{
			throw new DAOException("Customer could not be updated!", e);
		}
		return recordsAffected;
	}

	private int insert(Connection connection, Kunden kunden)
	{
		validateConnection(connection);
		validateKunden(kunden);

		int recordsAffected = 0;
		Date now = new Date();
		ResultSet generatedKeys = null;

		try
		{
			if (insertStmt == null)
			{
				insertStmt = connection.prepareStatement(SQL_INSERT,
						PreparedStatement.RETURN_GENERATED_KEYS);
			}

			insertStmt.setTimestamp(1, JDBCHelper.toSqlTimestamp(now));
			insertStmt.setString(2, kunden.getEMAIL());
			insertStmt.setString(3, kunden.getANREDE());
			insertStmt.setString(4, kunden.getNAME());
			insertStmt.setString(5, kunden.getPASSWORT());
			insertStmt.setString(6, kunden.getSTRASSE());
			insertStmt.setLong(7, kunden.getPlzId());

			recordsAffected = insertStmt.executeUpdate();
			generatedKeys = insertStmt.getGeneratedKeys();

			if (generatedKeys.next())
			{
				long currentId = generatedKeys.getLong(1);
				kunden.setId(currentId);
				kunden.setLastModifiedTS(now);
			}
		} catch (SQLException e)
		{
			throw new DAOException("Customer could not be inserted!", e);
		} finally
		{
			closeResultSet(generatedKeys);
		}
		return recordsAffected;
	}

	private void validateKunden(Kunden kunden)
	{
		if (kunden == null)
			throw new IllegalArgumentException(
					"Zu speichernde Kunde darf nicht null sein");

	}

}
